{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT names\n",
    "\n",
    "## Pattern Matching Strings\n",
    "This tutorial uses the **LIKE** operator to check names. We will be using the SELECT command on the table world:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "\n",
    "You can use `WHERE name LIKE 'B%'` to find the countries that start with \"B\".\n",
    "\n",
    "The % is a _wild-card_ it can match any characters\n",
    "\n",
    "**Find the country that start with Y**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yemen</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Yemen',)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE 'Y%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "\n",
    "**Find the countries that end with y**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hungary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uruguay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vatican City</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Germany',),\n",
       " ('Hungary',),\n",
       " ('Italy',),\n",
       " ('Norway',),\n",
       " ('Paraguay',),\n",
       " ('Turkey',),\n",
       " ('Uruguay',),\n",
       " ('Vatican City',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%y';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "\n",
    "Luxembourg has an **x** - so does one other country. List them both.\n",
    "\n",
    "**Find the countries that contain the letter x**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Luxembourg',), ('Mexico',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%x%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "\n",
    "Iceland, Switzerland end with **land** - but are there others?\n",
    "\n",
    "**Find the countries that end with land**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iceland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>New Zealand</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Swaziland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Switzerland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thailand</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Finland',),\n",
       " ('Iceland',),\n",
       " ('Ireland',),\n",
       " ('New Zealand',),\n",
       " ('Poland',),\n",
       " ('Swaziland',),\n",
       " ('Switzerland',),\n",
       " ('Thailand',)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%land';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "\n",
    "Columbia starts with a **C** and ends with **ia** - there are two more like this.\n",
    "\n",
    "**Find the countries that start with C and end with ia**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cambodia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Colombia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Croatia</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Cambodia',), ('Colombia',), ('Croatia',)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE 'C%ia';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Greece has a double **e** - who has **a** double **o**?\n",
    "\n",
    "**Find the country that has oo in the name**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cameroon</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Cameroon',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%oo%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "\n",
    "Bahamas has three **a** - who else?\n",
    "\n",
    "**Find the countries that have three or more a in the name**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "21 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Antigua and Barbuda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahamas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bosnia and Herzegovina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Equatorial Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jamaica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kazakhstan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Madagascar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Malaysia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marshall Islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mauritania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Micronesia, Federated States of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nicaragua</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Papua New Guinea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Vincent and the Grenadines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saudi Arabia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tanzania</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Antigua and Barbuda',),\n",
       " ('Bahamas',),\n",
       " ('Bosnia and Herzegovina',),\n",
       " ('Canada',),\n",
       " ('Equatorial Guinea',),\n",
       " ('Guatemala',),\n",
       " ('Jamaica',),\n",
       " ('Kazakhstan',),\n",
       " ('Madagascar',),\n",
       " ('Malaysia',),\n",
       " ('Marshall Islands',),\n",
       " ('Mauritania',),\n",
       " ('Micronesia, Federated States of',),\n",
       " ('Nicaragua',),\n",
       " ('Panama',),\n",
       " ('Papua New Guinea',),\n",
       " ('Paraguay',),\n",
       " ('Saint Vincent and the Grenadines',),\n",
       " ('Saudi Arabia',),\n",
       " ('Tanzania',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%a%a%a%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "\n",
    "India and Angola have an **n** as the second character. You can use the underscore as a single character wildcard.\n",
    "\n",
    "```sql\n",
    "SELECT name FROM world\n",
    " WHERE name LIKE '_n%'\n",
    "ORDER BY name\n",
    "```\n",
    "\n",
    "**Find the countries that have \"t\" as the second character.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethiopia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ethiopia',), ('Italy',)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '_t%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "\n",
    "Lesotho and Moldova both have two o characters separated by two other characters.\n",
    "\n",
    "**Find the countries that have two \"o\" characters separated by two others.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Congo, Democratic Republic of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Congo, Republic of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lesotho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Moldova</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mongolia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Morocco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sao Tomé and Príncipe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Congo, Democratic Republic of',),\n",
       " ('Congo, Republic of',),\n",
       " ('Lesotho',),\n",
       " ('Moldova',),\n",
       " ('Mongolia',),\n",
       " ('Morocco',),\n",
       " ('Sao Tomé and Príncipe',)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%o__o%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "\n",
    "Cuba and Togo have four characters names.\n",
    "\n",
    "**Find the countries that have exactly four characters.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cuba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fiji</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iraq</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Laos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mali</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Togo</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Chad',),\n",
       " ('Cuba',),\n",
       " ('Fiji',),\n",
       " ('Iran',),\n",
       " ('Iraq',),\n",
       " ('Laos',),\n",
       " ('Mali',),\n",
       " ('Oman',),\n",
       " ('Peru',),\n",
       " ('Togo',)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '____';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "\n",
    "The capital of **Luxembourg** is **Luxembourg**. Show all the countries where the capital is the same as the name of the country\n",
    "\n",
    "**Find the country where the name is the capital city.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Marino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Singapore</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Djibouti',), ('Luxembourg',), ('San Marino',), ('Singapore',)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name=capital;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "\n",
    "The capital of **Mexico** is **Mexico City**. Show all the countries where the capital has the country together with the word \"City\".\n",
    "\n",
    "**Find the country where the capital is the country plus \"City\".**\n",
    "\n",
    "> _The concat function_    \n",
    "> The function concat is short for concatenate - you can use it to combine two or more strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Guatemala',), ('Kuwait',), ('Mexico',), ('Panama',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE capital=CONCAT(name, ' City');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "\n",
    "**Find the capital and the name where the capital includes the name of the country.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>capital</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra la Vella</td>\n",
       "        <td>Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Djibouti</td>\n",
       "        <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala City</td>\n",
       "        <td>Guatemala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait City</td>\n",
       "        <td>Kuwait</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Luxembourg</td>\n",
       "        <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico City</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco-Ville</td>\n",
       "        <td>Monaco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama City</td>\n",
       "        <td>Panama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Marino</td>\n",
       "        <td>San Marino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Singapore</td>\n",
       "        <td>Singapore</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Andorra la Vella', 'Andorra'),\n",
       " ('Djibouti', 'Djibouti'),\n",
       " ('Guatemala City', 'Guatemala'),\n",
       " ('Kuwait City', 'Kuwait'),\n",
       " ('Luxembourg', 'Luxembourg'),\n",
       " ('Mexico City', 'Mexico'),\n",
       " ('Monaco-Ville', 'Monaco'),\n",
       " ('Panama City', 'Panama'),\n",
       " ('San Marino', 'San Marino'),\n",
       " ('Singapore', 'Singapore')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT capital, name FROM world WHERE capital LIKE CONCAT('%', name, '%');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "\n",
    "**Find the capital and the name where the capital is an extension of name of the country.**\n",
    "\n",
    "You _should_ include **Mexico City** as it is longer than **Mexico**. You _should not_ include **Luxembourg** as the capital is the same as the country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>capital</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra la Vella</td>\n",
       "        <td>Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala City</td>\n",
       "        <td>Guatemala</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait City</td>\n",
       "        <td>Kuwait</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico City</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco-Ville</td>\n",
       "        <td>Monaco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama City</td>\n",
       "        <td>Panama</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Andorra la Vella', 'Andorra'),\n",
       " ('Guatemala City', 'Guatemala'),\n",
       " ('Kuwait City', 'Kuwait'),\n",
       " ('Mexico City', 'Mexico'),\n",
       " ('Monaco-Ville', 'Monaco'),\n",
       " ('Panama City', 'Panama')]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT capital, name FROM world WHERE capital LIKE CONCAT('%', name, '%') AND capital <> name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "\n",
    "For **Monaco-Ville** the name is **Monaco** and the extension is **-Ville**.\n",
    "\n",
    "**Show the name and the extension where the capital is an extension of name of the country.**\n",
    "\n",
    "You can use the SQL function [REPLACE](https://sqlzoo.net/wiki/REPLACE)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>ext</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra</td>\n",
       "        <td> la Vella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "        <td> City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait</td>\n",
       "        <td> City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td> City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>-Ville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "        <td> City</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Andorra', ' la Vella'),\n",
       " ('Guatemala', ' City'),\n",
       " ('Kuwait', ' City'),\n",
       " ('Mexico', ' City'),\n",
       " ('Monaco', '-Ville'),\n",
       " ('Panama', ' City')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, REPLACE(capital, name, '') AS ext FROM world\n",
    "WHERE capital LIKE CONCAT(name, '%') AND capital <> name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
